package cn.javabb.framework.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.javabb.common.constant.GenConstants;
import cn.javabb.common.exception.GenException;
import cn.javabb.framework.config.DataSourceConfig;
import cn.javabb.framework.config.IDbQuery;
import cn.javabb.framework.config.IKeyWordsHandler;
import cn.javabb.framework.rules.DbType;
import cn.javabb.project.entity.GenTable;
import cn.javabb.project.entity.GenTableColumn;
import cn.javabb.project.entity.TableField;
import cn.javabb.project.entity.TableInfo;
import cn.javabb.project.util.GenUtil;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @desc: 数据库工具
 * @author: javabb (javabob(a)163.com)
 * @create: 2021/04/26 09:47
 */
@Slf4j
public class DBUtil {
    // SQL连接
    Connection connection;
    // 数据库连接配置
    private DataSourceConfig dataSourceConfig;

    public DBUtil(DataSourceConfig dsc) {
        this.dataSourceConfig = dsc;
        connection = dsc.getConn();
    }

    /**
     * 获取数据库表
     *
     * @return
     */
    public List<GenTable> listTable(List<String> names) throws SQLException {
        //所有表信息
        List<GenTable> tableList = new ArrayList<>();
        IDbQuery dbQuery = this.dataSourceConfig.getDbQuery();
        try {
            String tablesSql;
            if (CollUtil.isNotEmpty(names)) {
                tablesSql = dataSourceConfig.getDbQuery().tablesSqlByNames(names);
            } else {
                tablesSql = dataSourceConfig.getDbQuery().tablesSql();
            }
            StringBuilder sql = new StringBuilder(tablesSql);
            GenTable table;
            try (
                    PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
                    ResultSet results = preparedStatement.executeQuery();
            ) {
                while (results.next()) {
                    String tableName = results.getString(dbQuery.tableName());
                    if (StrUtil.isNotBlank(tableName)) {
                        table = new GenTable();
                        table.setTableName(tableName);
                        String commentColumn = dbQuery.tableComment();
                        if (StrUtil.isNotBlank(commentColumn)) {
                            String tableComment = results.getString(commentColumn);
                            if ("VIEW".equals(tableComment)) {
                                // 跳过视图
                                continue;
                            }
                            //备注
                            table.setTableComment(GenUtil.replaceText(tableComment));
                        }
                        tableList.add(table);
                    } else {
                        log.debug("当前数据库为空");
                    }
                }
            }
        } catch (Exception e) {
            throw new GenException("数据库表读取错误:" + e.getMessage());
        }finally {
            connection.close();
        }
        return tableList;
    }

    /**
     * 根据表名获取数据库的列信息
     *
     * @param table
     * @return
     */
    public List<GenTableColumn> listTableColumn(GenTable table) throws SQLException {
        boolean havePk = false;
        List<GenTableColumn> tableColumns = new ArrayList<>();
        DbType dbType = this.dataSourceConfig.getDbType();
        IDbQuery dbQuery = this.dataSourceConfig.getDbQuery();
        String tableName = table.getTableName();
        try {
            String tableColumnSql = dbQuery.tableFieldsSql();
            if (DbType.ORACLE.equals(dbType)) {
                tableName = tableName.toUpperCase();
                tableColumnSql = String.format(tableColumnSql.replace("#schema", dataSourceConfig.getSchemaName()), tableName);
            } else if (DbType.H2.equals(dbType)) {

            } else {
                // 先考虑MySQL
                tableColumnSql = String.format(tableColumnSql, tableName);
            }
            try (
                    PreparedStatement preparedStatement = connection.prepareStatement(tableColumnSql);
                    ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    GenTableColumn column = new GenTableColumn();
                    column.setTableId(table.getTableId());
                    String columnName = resultSet.getString(dbQuery.fieldName());
                    // 避免多重主键设置，目前只取第一个找到ID，并放到list中的索引为0的位置
                    String key = resultSet.getString(dbQuery.fieldKey());
                    boolean isPk = StrUtil.isNotBlank(key) && "PRI".equals(key.toUpperCase());
                    //处理主键
                    if (isPk && !havePk) {
                        havePk = true;
                        column.setIsPk(GenConstants.REQUIRE);
                        if (dbQuery.isKeyIdentity(resultSet)) {
                            // 是否是自增
                            column.setIsIncrement(GenConstants.REQUIRE);
                        }
                    }
                    // 列属性名
                    String newColumnName = columnName;
                    IKeyWordsHandler keyWordsHandler = this.dataSourceConfig.getKeyWordsHandler();
                    if (keyWordsHandler != null) {
                        if (keyWordsHandler.isKeyWords(columnName)) {
                            System.err.println(String.format("当前表[%s]存在字段[%s]为数据库关键字或保留字!", tableName, columnName));
                            column.setKeywords(true);
                            //newColumnName = keyWordsHandler.formatColumn(columnName);
                        }
                    }
                    column.setColumnName(newColumnName);
                    // 设置Java字段名
                    column.setJavaField(StrUtil.toCamelCase(columnName));
                    // 列类型,一般是int(11),varchar(20),datetime
                    String columnType = resultSet.getString(dbQuery.fieldType());
                    // 将列类型的后面()去掉
                    String dataType = GenUtil.getDbType(columnType);
                    column.setColumnType(columnType);
                    // 设置Java字段类型
                    column.setJavaType(GenConstants.TYPE_STRING); //默认String类型
                    if (GenUtil.arraysContains(GenConstants.COLUMNTYPE_STR, dataType)
                            || GenUtil.arraysContains(GenConstants.COLUMNTYPE_TEXT, dataType)) {
                        Integer columnLength = GenUtil.getColumnLength(columnType);
                        String htmlType = columnLength >= 500 || GenUtil.arraysContains(GenConstants.COLUMNTYPE_TEXT, dataType) ? GenConstants.HTML_TEXTAREA : GenConstants.HTML_INPUT;
                        column.setHtmlType(htmlType);
                    } else if (GenUtil.arraysContains(GenConstants.COLUMNTYPE_TIME, dataType)) {
                        column.setJavaType(GenConstants.TYPE_DATE);
                        column.setHtmlType(GenConstants.HTML_DATETIME);
                    } else if (GenUtil.arraysContains(GenConstants.COLUMNTYPE_NUMBER, dataType)) {
                        column.setHtmlType(GenConstants.HTML_INPUT);
                        // 如果是浮点型,统一使用BigDecimal
                        String[] str = StrUtil.split(StrUtil.subBetween(columnType, "(", ")"), ",");
                        if (str != null && str.length == 2 && Integer.parseInt(str[1]) > 0) {
                            column.setJavaType(GenConstants.TYPE_BIGDECIMAL);
                        }
                        // 如果是整形
                        else if (str != null && str.length == 1 && Integer.parseInt(str[0]) <= 10) {
                            column.setJavaType(GenConstants.TYPE_INTEGER);
                        }
                        // 长整形
                        else {
                            column.setJavaType(GenConstants.TYPE_LONG);
                        }
                    }
                    if (dbQuery.isNotNull(resultSet)) {
                        column.setIsRequired(GenConstants.REQUIRE);
                    }
                    // 是否插入字段,(默认所有字段都需要插入)
                    column.setIsInsert(GenConstants.REQUIRE);
                    // 编辑字段
                    if (!GenUtil.arraysContains(GenConstants.COLUMNNAME_NOT_EDIT, columnName) && !column.isPk()) {
                        column.setIsEdit(GenConstants.REQUIRE);
                    }
                    // 列表字段
                    if (!GenUtil.arraysContains(GenConstants.COLUMNNAME_NOT_LIST, columnName) && !column.isPk()) {
                        column.setIsList(GenConstants.REQUIRE);
                    }
                    // 查询字段
                    if (!GenUtil.arraysContains(GenConstants.COLUMNNAME_NOT_QUERY, columnName) && !column.isPk()) {
                        column.setIsQuery(GenConstants.REQUIRE);
                    }
                    // 查询字段类型
                    if (StrUtil.endWithIgnoreCase(columnName, "name") ||
                            StrUtil.endWithIgnoreCase(columnName, "code")) {
                        column.setQueryType(GenConstants.QUERY_LIKE);
                    }
                    // 状态字段设置单选框
                    if (StrUtil.endWithIgnoreCase(columnName, "status") || StrUtil.endWithIgnoreCase(columnName, "state")) {
                        column.setHtmlType(GenConstants.HTML_RADIO);
                    }
                    // 类型&性别字段设置下拉框
                    else if (StrUtil.endWithIgnoreCase(columnName, "type")
                            || StrUtil.endWithIgnoreCase(columnName, "sex")) {
                        column.setHtmlType(GenConstants.HTML_SELECT);
                    }
                    // 文件字段设置上传控件
                    else if (StrUtil.endWithIgnoreCase(columnName, "file")) {
                        column.setHtmlType(GenConstants.HTML_UPLOAD);
                    }
                    // 设置列注释
                    String commentColumn = dbQuery.fieldComment();
                    column.setColumnComment(GenUtil.replaceText(resultSet.getString(commentColumn)));
                    tableColumns.add(column);
                }
            }
        } catch (Exception e) {
            throw new GenException("数据库表字段获取失败", e);
        }finally {
            connection.close();
        }
        return tableColumns;
    }

}